package com.dream.xiaobo.dao.impl;

import com.dream.xiaobo.dao.OrdersDao;
import com.dream.xiaobo.entity.*;
import com.dream.xiaobo.utils.C3P0Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author xiaobo
 * @date 2021/9/2 - 15:09
 */
public class OrdersDaoImpl implements OrdersDao {

    //创建QueryRunner实例化对象
    private QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    /**
     * 插入订单
     * @param orders
     * @throws SQLException
     */
    @Override
    public void insertOrder(Orders orders) throws SQLException {

        //插入订单的SQL
        String sql = "INSERT INTO orders(o_id,o_count,o_state,u_id,a_id,o_time) VALUES(?,?,?,?,?,?)";

        //执行SQL
        queryRunner.update(sql,orders.getoId(),orders.getoCount(),orders.getoState(),orders.getuId(),orders.getaId(),orders.getoTime());

    }

    /**
     * 插入订单项
     * @param items
     * @throws SQLException
     */
    @Override
    public void insertItem(List<Item> items) throws SQLException {

        //插入订单项SQL
        String sql = "INSERT INTO item(i_count,i_num,p_id,o_id) VALUES(?,?,?,?)";

        //创建一个二维数组用于存放订单项
        Object[][] params = new Object[items.size()][];

        //遍历数组
        for (int i = 0; i < items.size(); i++) {
            //获取订单的订单项
            Item item = items.get(i);
            //依次给每个订单的订单项赋值
            params[i] = new Object[]{item.getiCount(),item.getiNum(),item.getpId(),item.getoId()};

        }
        //执行SQL
        queryRunner.batch(sql,params);
    }

    /**
     * 根据用户Id查询订单信息
     * @param uId
     * @return
     * @throws SQLException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    @Override
    public List<Orders> selectOrderByUId(Integer uId) throws SQLException, InvocationTargetException, IllegalAccessException {

        //根据用户Id查询订单信息的SQL
        String sql = "SELECT o.o_id AS oId,o.o_count AS oCount,o.o_state AS oState," +
                "o.o_time AS oTime,o.u_id AS uId,o.a_id AS aId,a.a_name AS aName," +
                "a.a_phone AS aPhone,a.a_detail AS aDetail,a.` a_state` AS aState " +
                "FROM orders o JOIN address a ON o.a_id = a.a_id WHERE o.u_id = ?";

        //将查询到的信息存放到集合中
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(), uId);

        //当查询结果为空时、返回空
        if (list == null) {
            return null;
        }

        //创建订单集合
        List<Orders> ordersList = new ArrayList<>();

        //遍历查询的结果
        for (Map<String, Object> map : list) {

            //创建订单实例化对象
            Orders orders = new Orders();
            //创建地址实例化对象
            Address address = new Address();

            //属性和数据库字段映射
            BeanUtils.populate(orders,map);
            //属性和数据库字段映射
            BeanUtils.populate(address,map);
            //将地址添加到订单中
            orders.setAddress(address);
            //将订单添加到集合中
            ordersList.add(orders);
        }
        //返回查询到的结果
        return ordersList;
    }

    /**
     * 根据订单Id查询用户信息实现方法
     * @param oId
     * @return
     * @throws SQLException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    @Override
    public Orders selectOrderByOId(String oId) throws SQLException, InvocationTargetException, IllegalAccessException {

        //根据订单Id查询用户信息实现方法的SQL
        String sql = "SELECT o.o_id AS oId,o.o_count AS oCount,o.o_state AS oState," +
                "o.o_time AS oTime,o.u_id AS uId,o.a_id AS aId,a.a_name AS aName," +
                "a.a_phone AS aPhone,a.a_detail AS aDetail,a.` a_state` AS aState " +
                "FROM orders o JOIN address a ON o.a_id = a.a_id WHERE o.o_id = ?";

        //执行SQL
        Map<String, Object> map = queryRunner.query(sql, new MapHandler(), oId);
        //创建订单实例化对象
        Orders orders = new Orders();
        //创建地址实例化对象
        Address address = new Address();

        //将数据库字段与实体类属性映射
        BeanUtils.populate(orders,map);
        //将数据库字段与实体类属性映射
        BeanUtils.populate(address,map);

        //将地址信息放进订单中
        orders.setAddress(address);

        //返回订单信息
        return orders;
    }

    /**
     * 根据订单Id查询订单项实现方法
     * @param oId
     * @return
     * @throws SQLException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    @Override
    public List<Item> selectItemByOId(String oId) throws SQLException, InvocationTargetException, IllegalAccessException {

        //根据订单Id查询订单项
        String sql = "SELECT i.i_id AS iId,i.i_count AS iCount,i.i_num AS iNum," +
                "i.p_id AS pId,i.o_id AS oId,p.p_id AS pId,p.p_name As pName," +
                "p.p_time AS pTime,p.p_image AS pImage,p.p_price AS pPrice," +
                "p.p_state AS pState,p.p_info AS pInfo,p.t_id AS tId " +
                "FROM item i JOIN product p ON i.p_id = p.p_id WHERE i.o_id = ?";

        //执行SQL
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(), oId);

        //判断集合是否为空
        if (list == null) {
            return null;
        }

        //创建Item集合实例化对象
        List<Item> itemList = new ArrayList<>();

        //遍历集合
        for (Map<String, Object> map : list) {

            //创建订单项实例化对象
            Item item = new Item();
            //创建商品实例化对象
            Product product = new Product();

            //映射
            BeanUtils.populate(item,map);
            BeanUtils.populate(product,map);

            //将商品放进订单项中
            item.setProduct(product);
            //将订单项放进List集合
            itemList.add(item);
        }
        //返回订单项集合
        return itemList;
    }

    /**
     * 根据订单Id更新订单实现方法
     * @param oId
     * @throws SQLException
     */
    @Override
    public void updateOrderByOId(String oId) throws SQLException {

        //根据订单Id更新订单SQL
        String sql = "UPDATE orders SET o_state = 2 WHERE o_id = ?";
        //执行SQL
        queryRunner.update(sql,oId);
    }

    /**
     * 查询订单全部信息实现方法
     * @return
     * @throws SQLException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    @Override
    public List<Orders> selectOrder() throws SQLException, InvocationTargetException, IllegalAccessException {

        //查询订单全部信息实现方法SQL
        String sql = "SELECT o.o_id AS oId,o.o_count AS oCount,o.o_state AS oState," +
                "o.u_id AS uId,o.a_id AS aId,o.o_time AS oTime,u.u_name AS uName " +
                "FROM orders o,user u WHERE o.u_id = u.u_id";

        //执行SQL
        List<Map<String, Object>> lists = queryRunner.query(sql, new MapListHandler());
        //创建订单集合
        List<Orders> list = new ArrayList<>();
        //遍历集合
        for (Map<String, Object> map : lists) {
            //创建订单实例化对象
            Orders orders = new Orders();
            //创建用户实例化对象
            User user = new User();
            //映射
            BeanUtils.populate(orders,map);
            BeanUtils.populate(user,map);
            //将用户放进订单中
            orders.setUser(user);
            //将订单放进集合中
            list.add(orders);
        }
        //返回查询到的信息
        return list;
    }

    /**
     * 根据订单Id更新实现方法
     * @param oId
     * @throws SQLException
     */
    @Override
    public void updateOrderByOidHair(String oId) throws SQLException {

        //根据订单Id更新SQL
        String sql = "UPDATE orders SET o_state = 3 WHERE o_id = ?";

        //执行SQL
        queryRunner.update(sql,oId);
    }

    /**
     * 根据订单Id更新实现方法
     * @param oId
     * @throws SQLException
     */
    @Override
    public void updateOrderByOIdCollect(String oId) throws SQLException {

        //根据订单Id更新SQL
        String sql = "UPDATE orders SET o_state = 4 WHERE o_id = ?";

        //执行SQL
        queryRunner.update(sql,oId);
    }
}
